create database OnlineAptitudeTestDatabase
go
use OnlineAptitudeTestDatabase
go
create table UserInfo(
	[UserID] int identity(1,1) primary key,
	[UserName] varchar(20) not null unique,
	[Password] varchar(100) not null,
	[NameV] nvarchar(50) not null,
	[NameE] varchar(50) not null,
	[Email] varchar(50),
	[Phone] varchar(15),
	[Address] nvarchar(100),
	[EducationDetails] ntext,
	[WorkExperience] ntext,
	[FirstLogin] bit default 1 not null,
	[Login] bit default 0 not null,
	[Active] bit default 1 not null
)
go
create table ManagerInfo(
	[ManagerID] int identity(1,1) primary key,
	[UserName] varchar(20) not null unique,
	[Password] varchar(100) not null,
	[NameV] nvarchar(50) not null,
	[NameE] varchar(50) not null,
	[Email] varchar(50),
	[Phone] varchar(15),
	[Address] nvarchar(100),
	[FirstLogin] bit default 1 not null,
	[Login] bit default 0 not null,
	[Active] bit default 1 not null
)
go
create table ExamsInfo(
	[ExamsID] int identity(1,1) primary key,
	[NameV] nvarchar(50) not null,
	[NameE] varchar(50) not null,
	[TimeBegin] datetime not null,
	[TimeEnd] datetime not null,
	[Benchmark] tinyint not null,
	[Creater] int references ManagerInfo([ManagerID]),
	[CreateTime] datetime not null,
	[Active] bit default 1 not null
)
go
create table QuestionsInfo(
	[QuestionsID] int identity(1,1) primary key,
	[ContentV] ntext not null,
	[ContentE] text not null,
	[Level] tinyint not null,
	[Type] tinyint not null,
	[Choices] xml,
	[CorrectAnswer] varchar(10) not null,
	[Creater] int references ManagerInfo([ManagerID]),
	[CreateTime] datetime not null,
	[Active] bit default 1 not null
)
go
create table Exam_Question(
	[ExamsID] int references ExamsInfo([ExamsID]),
	[QuestionsID] int references QuestionsInfo([QuestionsID]),
	[Active] bit default 1,
	primary key(ExamsID,QuestionsID)
)
go
create table Results(
	[ExamsID] int references ExamsInfo([ExamsID]),
	[UserID] int references UserInfo([UserID]),
	[Time] datetime,
	[Mark] tinyint,
	[Complete] bit default 0,
	[Active] bit default 1,
	primary key(ExamsID,UserID)
)